<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h1 data-lake-id="JPmyC" id="JPmyC"><span data-lake-id="ub3866bdc" id="ub3866bdc">典型回答</span></h1>
  <p data-lake-id="ud9030cc7" id="ud9030cc7"><br></p>
  <p data-lake-id="u0114d4da" id="u0114d4da"><span data-lake-id="ub7756794" id="ub7756794">当我们要清理表中的历史数据时，一般都是通过时间来进行判断的，执行delete的语句如下：</span></p>
  <p data-lake-id="u3c8cbfc7" id="u3c8cbfc7"><span data-lake-id="u4a8b52dc" id="u4a8b52dc">​</span><br></p>
  <pre lang="java"><code>
DELETE FROM table_hollis WHERE `gmt_create` &lt; SUBDATE(CURDATE(),INTERVAL 300 DAY);
</code></pre>
  <p data-lake-id="uefee835b" id="uefee835b"><span data-lake-id="u61d22aeb" id="u61d22aeb">​</span><br></p>
  <p data-lake-id="u8c73bf42" id="u8c73bf42"><span data-lake-id="u7eabf3a2" id="u7eabf3a2">如上SQL，就是删除300天之前的数据，如果是小表的话，执行这个SQL没啥问题，但是如果是大表，如果表中的数据量达到千万级别的话，就会有问题了。</span></p>
  <p data-lake-id="udd304025" id="udd304025"><span data-lake-id="u0f84b227" id="u0f84b227">​</span><br></p>
  <p data-lake-id="u542f6ecf" id="u542f6ecf"><span data-lake-id="u0c0f4669" id="u0c0f4669">像以上这样的SQL，如果没有在gmt_create字段上创建索引，那么delete操作就会导致</span><strong><span data-lake-id="u79f83aa4" id="u79f83aa4">锁表</span></strong><span data-lake-id="u779c9b97" id="u779c9b97">，而锁表给业务带来的影响就是业务都无法进行写操作了，这肯定是无法接受的。</span></p>
  <p data-lake-id="u612858be" id="u612858be"><span data-lake-id="u125cda65" id="u125cda65">​</span><br></p>
  <p data-lake-id="u447fa7aa" id="u447fa7aa"><span data-lake-id="ucc924ee3" id="ucc924ee3">而且，即使业务说我可以允许锁表，上面的操作也有可能会失败，因为</span><strong><span data-lake-id="u8883536e" id="u8883536e">数据库会对单条SQL产生的bin_log有大小是有限制的</span></strong><span data-lake-id="ub66e7669" id="ub66e7669">，删除这么大量的数据，产生的日志大小如果超过该阈值，最终还是会失败！</span></p>
  <p data-lake-id="u05259649" id="u05259649"><span data-lake-id="u06b4b594" id="u06b4b594">​</span><br></p>
  <blockquote data-lake-id="ub33bd0a3" id="ub33bd0a3">
   <p data-lake-id="u67b08f54" id="u67b08f54"><span data-lake-id="u5d9ae127" id="u5d9ae127" class="lake-fontsize-12" style="color: rgb(24, 24, 24)">max_binlog_cache_size参数指定了单个事务最大允许使用的Binlog，当超出这个值时，会出现报错：Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again</span></p>
  </blockquote>
  <p data-lake-id="ue1254ac9" id="ue1254ac9"><br></p>
  <p data-lake-id="u68bed6bb" id="u68bed6bb"><span data-lake-id="ucc713b9a" id="ucc713b9a">而且，删除操作还涉及到磁盘IO，如果要删除的数据太多，就会导致频繁的IO，对数据也会造成一定的压力。</span></p>
  <p data-lake-id="u823b5d5a" id="u823b5d5a"><span data-lake-id="ub530a562" id="ub530a562">​</span><br></p>
  <p data-lake-id="u0c995104" id="u0c995104"><span data-lake-id="u850c4345" id="u850c4345">还有就是，数据的删除过程，也会伴随着索引更新，大量的数据删除操作，会因为频繁的索引重建而导致业务无法进行写操作。</span></p>
  <p data-lake-id="ubf5a6cce" id="ubf5a6cce"><span data-lake-id="uaf7aa17e" id="uaf7aa17e">​</span><br></p>
  <p data-lake-id="uf0470636" id="uf0470636"><span data-lake-id="u4a573237" id="u4a573237">那么，怎么解决呢？如何实现高效、安全的大表的批量删除呢？</span></p>
  <p data-lake-id="u917f92d9" id="u917f92d9"><span data-lake-id="u8102f10f" id="u8102f10f">​</span><br></p>
  <p data-lake-id="u9bca6bd7" id="u9bca6bd7"><span data-lake-id="u4f3ffb89" id="u4f3ffb89">这里可以参考阿里云DMS的数据清理功能的方案（我司内部的数据库用的就是你这个方案——</span><a href="https://help.aliyun.com/document_detail/162507.html" target="_blank" data-lake-id="uce64eaf8" id="uce64eaf8"><span data-lake-id="u13df4241" id="u13df4241">https://help.aliyun.com/document_detail/162507.html</span></a><span data-lake-id="u566181a0" id="u566181a0"> ）</span></p>
  <p data-lake-id="ufe94dcee" id="ufe94dcee"><span data-lake-id="u149c4d9b" id="u149c4d9b">​</span><br></p>
  <p data-lake-id="uf5c90c15" id="uf5c90c15"><span data-lake-id="ud48ea7b2" id="ud48ea7b2">他的做法总结一句话就是：DMS在清理数据时会扫描全表，根据主键或非空唯一键分批执行。</span></p>
  <p data-lake-id="uf73f91fb" id="uf73f91fb"><span data-lake-id="u72bdcfc7" id="u72bdcfc7">​</span><br></p>
  <p data-lake-id="u2aa5e74c" id="u2aa5e74c"><span data-lake-id="uacce3124" id="uacce3124">1、获取要做数据清理的表的主键，或者非空唯一键的最大值和最小值。</span></p>
  <p data-lake-id="uc95e7eac" id="uc95e7eac"><span data-lake-id="u82524eb7" id="u82524eb7">​</span><br></p>
  <p data-lake-id="ue95d5e7b" id="ue95d5e7b"><span data-lake-id="u68e34d61" id="u68e34d61">如：</span></p>
  <pre lang="java"><code>
select min(id) as min_id,max(id) as max_id from table_hollis;
</code></pre>
  <p data-lake-id="u16c2b661" id="u16c2b661"><br></p>
  <p data-lake-id="u8d5c8b4e" id="u8d5c8b4e"><span data-lake-id="uc0ffce19" id="uc0ffce19">假如我们得到min_id = 100，max_id = 100000；</span></p>
  <p data-lake-id="u85404633" id="u85404633"><span data-lake-id="ufc4ab8f8" id="ufc4ab8f8">​</span><br></p>
  <p data-lake-id="u4d3f67d8" id="u4d3f67d8"><span data-lake-id="ub6c6e82e" id="ub6c6e82e">2、分段取出第一个区间的所有数据，默认区间可能是1000，也可以根据binlog配置等进行调整。</span></p>
  <p data-lake-id="u2ad73e62" id="u2ad73e62"><span data-lake-id="u9d112abc" id="u9d112abc">​</span><br></p>
  <pre lang="java"><code>
select id,( //查出符合条件的数据
            select 1 from (
              //查出第一个区间的所有gmt_create
              select gmt_create from table_hollis
              where id &gt;= 100 and id &lt;= 100000 
              order by id asc limit 1000
            ) t where gmt_create &lt; SUBDATE(CURDATE(),INTERVAL 300 DAY) limit 1
          )  as hasNeedDelItem
from table_hollis
where id &gt;= 100 and id &lt;= 100000 
order by id asc limit 1000;
</code></pre>
  <p data-lake-id="udb7163cd" id="udb7163cd"><br></p>
  <p data-lake-id="u9d375f29" id="u9d375f29"><span data-lake-id="uc3420106" id="uc3420106">以上SQL（SQL看不懂的多看几遍就懂了，主要就是尽可能用到索引和减少扫描的数据量，然后确定命中的ID），如果在100，到1100这个范围内，存在符合条件的数据，那么得到结果中hasNeedDelItem就是1 ，否则就是null。</span></p>
  <p data-lake-id="u35b60bcd" id="u35b60bcd"><span data-lake-id="ub52cc48e" id="ub52cc48e">​</span><br></p>
  <p data-lake-id="u647dd3a7" id="u647dd3a7"><span data-lake-id="ud4637cff" id="ud4637cff">最终我们只需要把所有hasNeedDelItem=1的id都删除掉就行了。</span></p>
  <p data-lake-id="u47529383" id="u47529383"><span data-lake-id="u60b6191c" id="u60b6191c">​</span><br></p>
  <p data-lake-id="u23dcaf36" id="u23dcaf36"><span data-lake-id="uc897faad" id="uc897faad">这样，在按照ID删除的时候，就可以用到主键索引，进行删除，而且因为做了分批，也不会一次性删除大量数据。</span></p>
  <p data-lake-id="u590fd1bf" id="u590fd1bf"><span data-lake-id="uab8f49b2" id="uab8f49b2">​</span><br></p>
  <p data-lake-id="ua7c93a9e" id="ua7c93a9e"><span data-lake-id="uf8add022" id="uf8add022">在阿里云MDS的数据清理功能中，还可以设置开始执行时间和结束执行时间，只有在这个时间范围内才会执行，如果超过了这个时间，就不再执行了。也可以避免数据清理导致线上数据库不可用！</span></p>
 </body>
</html>